跳到主要内容

JDBC 回顾学习

什么是 JDBC？​

JDBC 是 Java DataBase Connectivity 的缩写，它是Java程序访问数据库的标准接口。

使用 Java 程序访问数据库时，Java 代码并不是直接通过 TCP 连接去访问数据库，而是通过 JDBC 接口来访问，而 JDBC 接口则通过 JDBC 驱动来实现真正对数据库的访问。

例如，我们在 Java 代码中如果要访问 MySQL，那么必须编写代码操作 JDBC 接口。注意到 JDBC 接口是 Java 标准库自带的，所以可以直接编译。

而具体的 JDBC 驱动是由数据库厂商提供的，例如，MySQL 的 JDBC 驱动由 Oracle 提供。因此，访问某个具体的数据库，我们只需要引入该厂商提供的 JDBC 驱动，就可以通过 JDBC 接口来访问，这样保证了 Java 程序编写的是一套数据库访问代码，却可以访问各种不同的数据库，因为他们都提供了标准的 JDBC 驱动：

┌ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┐

│  ┌───────────────┐  │
   │   Java App    │
│  └───────────────┘  │
           │
│          ▼          │
   ┌───────────────┐
│  │JDBC Interface │<─┼─── JDK
   └───────────────┘
│          │          │
           ▼
│  ┌───────────────┐  │
   │  JDBC Driver  │<───── Vendor
│  └───────────────┘  │
           │
└ ─ ─ ─ ─ ─│─ ─ ─ ─ ─ ┘
           ▼
   ┌───────────────┐
   │   Database    │
   └───────────────┘

MySQL 驱动​

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
    <scope>runtime</scope>
</dependency>

注意到这里添加依赖的 scope 是 runtime，因为编译 Java 程序并不需要 MySQL 的这个 jar 包，只有在运行期才需要使用。

注意：如果把 runtime 改成 compile，虽然也能正常编译，但是在 IDE 里写程序的时候，会多出来一大堆类似 com.mysql.jdbc.Connection 这样的类，非常容易与 Java 标准库的 JDBC 接口混淆，所以坚决不要设置为 compile。

JDBC基本连接查询流程​

创建测试表​

create database studyJDBC;
use studyJDBC;
create table users(
    id int primary key auto_increment,
    username varchar(40),
    password varchar(60),
    email varchar(60),
    birthday DATE
)engine=innodb default charset=utf8;

INSERT into users(username, password, email, birthday)
values
       ('张三','123456','zs@gmail.com','1980-12-05'),
       ('李四','123456','lisi@gmail.com','1981-12-04'),
       ('王五','123456','wangwu@gmail.com','1980-12-02');

创建一个 urlPath.properties 文件来方便修改（这个需要自己解析，如下一节的代码）

# urlPath.properties文件里
# useSSL=true使用安全的连接
url=jdbc:mysql://localhost:3306/studyJDBC?useUnicode=true&characterEncoding=utf8&useSSL=true&useServerPrepStmts=true
username=root
password=123456

URL是由数据库厂商指定的格式，例如，MySQL 的 URL 是：

jdbc:mysql://<hostname>:<port>/<db>?key1=value1&key2=value2

其它常用配置连接数据库

jdbc:mysql://localhost:3306/数据库名

# 设置编码
jdbc:mysql://127.0.0.1:3306/user?useUnicode=true&characterEncoding=utf8

# 携带上时区
jdbc:mysql://localhost:3306/testuser?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC

# 启用编译功能和允许 SSL 连接
jdbc:mysql://localhost:3306/my_fix_system?useUnicode=true&characterEncoding=utf8&useSSL=true&useServerPrepStmts=true

使用 JDBC 连接数据库​

/**
 * @author alsritter
 * @version 1.0
 **/
public class JdbcDemo1 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1. 加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2. 用户信息和url
        String url = Objects.requireNonNull(LoadDBUrl.local()).getProperty("url");
        String username = Objects.requireNonNull(LoadDBUrl.local()).getProperty("username");
        String password = Objects.requireNonNull(LoadDBUrl.local()).getProperty("password");
        //3.连接成功，数据库对象
        Connection connection =  DriverManager.getConnection(url,username,password);
        //4.执行SQL的对象
        Statement statement = connection.createStatement();
        //5.执行SQL的对象，查看结果
        String sql = "SELECT * FROM users";
        ResultSet resultSet = statement.executeQuery(sql);

        while (resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("name="+resultSet.getObject("username"));
            System.out.println("pwd="+resultSet.getObject("password"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birthday="+resultSet.getObject("birthday"));
        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }

    /*
        写一个加载资源的类，直接在文件里动态修改
     */
    static class LoadDBUrl {
        public static Properties local() {
            Properties properties = null;
            try (
                   InputStream fileReader = JdbcUtils.class.getResourceAsStream("/urlPath.properties");
            ) {
                properties = new Properties();
                properties.load(fileReader);
            } catch (IOException e) {
                e.printStackTrace();
            }
            return properties;
        }
    }

}

DriverManager 创建连接​

这个 DriverManager ，虽然名字看起来和驱动相关，但是它不是用来加载驱动的，而是用来创建与数据库的连接用

Connection connection =  DriverManager.getConnection(url,username,password);

因为 JDBC 连接是一种昂贵的资源，所以使用后要及时释放。使用 try (resource) 来自动释放 JDBC 连接是一个好方法

try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    ...
}

用于执行查询语句的工具​

JDBC 提供了 Statement、PreparedStatement 和 CallableStatement 三种方式来执行查询语句，其中 Statement 用于通用查询， PreparedStatement 用于执行参数化查询，而 CallableStatement 则是用于存储过程

Statement​

第一步，通过 Connection 提供的 createStatement() 方法创建一个 Statement 对象，用于执行一个查询；

第二步，执行 Statement 对象提供的 executeQuery("SELECT * FROM students") 并传入 SQL 语句，执行查询并获得返回的结果集，使用 ResultSet 来引用这个结果集；

第三步，反复调用 ResultSet 的 next() 方法并读取每一行结果。

// 注意，这种 try (resource) 可以写在一个括号里面，这里只是为了更加直观
try (Connection conn = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASSWORD)) {
    try (Statement stmt = conn.createStatement()) {
        try (ResultSet rs = stmt.executeQuery("SELECT id, grade, name, gender FROM students WHERE gender=1")) {
            while (rs.next()) {
                long id = rs.getLong(1); // 注意：索引从1开始
                long grade = rs.getLong(2);
                String name = rs.getString(3);
                int gender = rs.getInt(4);
            }
        }
    }
}

注意要点：

Statment 和 ResultSet 都是需要关闭的资源，因此嵌套使用 try (resource) 确保及时关闭；

rs.next() 用于判断是否有下一行记录，如果有，将自动把当前行移动到下一行（一开始获得 ResultSet 时当前行不是第一行）；

ResultSet 获取列时，索引从 1 开始而不是 0；

必须根据 SELECT 的列的对应位置来调用 getLong(1)，getString(2) 这些方法，否则对应位置的数据类型不对，将报错。

不过这个 Statement 也不只有查询数据这一个功能，它还提供了其它的方法

  • executeQuery 方法用来执行查询操作
  • execute 执行任何SQL
  • executeUpdate 更新、插入、删除 都是使用这个，返回一个受影响的行数
int num = statement.executeUpdate(sql);
if (num>0) {
    System.out.println("删除成功");
}

SQL 注入​

SQL 注入即是指 web 应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在 web 应用程序中事先定义好的查询语句的结尾上添加额外的 SQL 语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息

例如：

select count(1) from students where name='张三'

注入类似 张三' or '1=1 

select count(1) from students where name='张三' or '1=1'

PreparedStatement ★​

为了解决 SQL 注入有一个办法就是使用 PreparedStatement

上述登录 SQL 如果用 PreparedStatement 可以改写如下：

// 使用 ? 作为占位符
User login(String name, String pass) {
    ...
    String sql = "SELECT * FROM user WHERE login=? AND pass=?";
    PreparedStatement ps = conn.prepareStatement(sql);
    ps.setObject(1, name);
    ps.setObject(2, pass);
    ...
}

PreparedStatement 为什么能防止 SQL 注入​

之所以 PreparedStatement 能防止注入，是因为它把单引号转义了，变成了 \'，这样一来，就无法截断 SQL 语句，进而无法拼接 SQL 语句，基本上没有办法注入了。

PreparedStatement 所执行的操作？​

PreparedStatement 执行 connection.prepareStatement(sql) 后会对 SQL 语句进行预编译，预编译后，服务器会返回一个句柄

PreparedStatement 就是通过调用这个句柄来执行前面编译好的模板

如下代码所示：

String sql = "SELECT * FROM users WHERE username=?";
// 预编译SQL，先写SQL，然后不执行
PreparedStatement preparedStatement = connection.prepareStatement(sql);
// 参数从1开始
preparedStatement.setString(1,"张三");
ResultSet resultSet = preparedStatement.executeQuery();
preparedStatement.close();

等价于

-- 预编译（stmt1就是句柄）
PREPARE stmt1 FROM "SELECT * FROM users WHERE username=?";

-- 传入参数
SET @pc = '张三';

-- 执行
EXECUTE stmt1 USING @pc;

-- 释放编译
DEALLOCATE PREPARE stmt1;

注意：这里是否执行预编译还要看 url 是否启动了，如果没有启动，那就是一个普通的字符拼接，它的效率是不变的（具体看下面的解释）

预编译是怎么节省 sql 执行成本的？​

MySQL 执行脚本的大致过程如下：prepare（准备）-> optimize（优化）-> exec（物理执行）

tq2ets.png

这里的 prepare 也就是编译

当客户发送一条 SQL 语句给服务器后，服务器总是需要校验 SQL 语句的语法格式是否正确，然后把 SQL 语句编译成可执行的函数，最后才是执行 SQL 语句。

其中校验语法，和编译所花的时间可能比执行 SQL 语句花的时间还要多。

对于同一个 SQL 模板，如果能将 prepare 的结果缓存，以后如果再执行相同模板而参数不同的 SQL，就可以节省掉 prepare（准备）的环节，从而节省 SQL 执行的成本

-- 可以通过打开日志查看是否进行了编译
set global general_log = on

-- 可以看到日志如下
-- 2020-06-11T08:49:17.728786Z     64 Prepare   SELECT * FROM users WHERE username=?
-- 2020-06-11T08:49:17.732784Z     64 Execute   SELECT * FROM users WHERE username='张三'
-- 2020-06-11T08:49:17.741538Z     64 Execute   SELECT * FROM users WHERE username='张三'



-- 别忘了关闭日志
set global general_log = off

MySQL 是否默认开启预编译？​

MySQL 是否默认开启预编译，与 MySQL server 的版本无关，而与 MySQL Connector/J（驱动程序）的版本有关，Connector/J 5.0.5 及以后的版本默认不支持预编译，Connector/J 5.0.5之前的版本默认支持预编译。

Connector/J在 5.0.5 以后的版本，默认 useServerPrepStmts 参数为 false

而要开启预编译，必须 useServerPrepStmts=true

"jdbc:mysql://localhost:3306/tempDB?user=root&password=123456&useServerPrepStmts=true"

所以 MySQL Server 4.1 之前不支持。但是而且 5.0.5 之后的版本是默认关闭的，注意这个关闭指的是客户端调用参数没有设置。

如果开启预编译功能的话，还要开启客户端缓存功能，否则有害无利。

但是即使开了预编译功能也不会对程序带来多大改进，因为 MySQL 的服务端预编译是 session 级别的，也就是说除非你的单个 connection 进行了大量的重复某个 SQL 这个预编译功能才有意义。

PreparedStatement 真的 “预编译”了吗？​

总结：如果不配置 PreparedStatement 实际是个假的 PreparedStatement

这个类 PreparedStatement 有点特殊，当用户开启了 useServerPrepStmts=true 时是启动提前编译，后面传参都是传给这个编译好的模板的模式，能提高效率。

而当 useServerPrepStmts=false 时则是采用字符拼接再执行的模式，这时则 效率不变，就像 Statement 一样，但是它和 Statement 的区别在于它的 setString 之类的方法都有对特殊字符检查，所以如果不开 useServerPrepStmts 就是单层保险，开了则是双重保险

在 MySQL 开启日志功能后（看上节），查看两者的区别，如下所示

# 如果 useServerPrepStmts=false 可以看到并没有进行编译
Query   SELECT * FROM users WHERE username='张三'
Query   SELECT * FROM users WHERE username='张三'

# 如果 useServerPrepStmts=true 可以看到进行了编译，并后面的同样的语句都是直接执行的
Prepare SELECT * FROM users WHERE username=?
Execute SELECT * FROM users WHERE username='张三'
Execute SELECT * FROM users WHERE username='张三'

所以可见 PreparedStatement 是否预编译取决于是否开启

JDBC Batch 批量操作​

使用JDBC操作数据库的时候，经常会执行一些批量操作。

例如，一次性给会员增加可用优惠券若干，我们可以执行以下SQL代码：

INSERT INTO coupons (user_id, type, expires) VALUES (123, 'DISCOUNT', '2030-12-31');
INSERT INTO coupons (user_id, type, expires) VALUES (234, 'DISCOUNT', '2030-12-31');
INSERT INTO coupons (user_id, type, expires) VALUES (345, 'DISCOUNT', '2030-12-31');
INSERT INTO coupons (user_id, type, expires) VALUES (456, 'DISCOUNT', '2030-12-31');
...

实际上执行 JDBC 时，因为只有占位符参数不同，所以 SQL 实际上是一样的：

for (var params : paramsList) {
    PreparedStatement ps = conn.preparedStatement("INSERT INTO coupons (user_id, type, expires) VALUES (?,?,?)");
    ps.setLong(params.get(0));
    ps.setString(params.get(1));
    ps.setString(params.get(2));
    ps.executeUpdate();
}

类似的还有，给每个员工薪水增加10%～30%：

UPDATE employees SET salary = salary * ? WHERE id = ?

通过一个循环来执行每个 PreparedStatement 虽然可行，但是性能很低。SQL 数据库对 SQL 语句相同，但只有参数不同的若干语句可以作为 batch 执行，即批量执行，这种操作有特别优化，速度远远快于循环执行每个 SQL。

在 JDBC 代码中，可以利用 SQL 数据库的这一特性，把同一个 SQL 但参数不同的若干次操作合并为一个 batch 执行。我们以批量插入为例，示例代码如下：

try (PreparedStatement ps = conn.prepareStatement("INSERT INTO students (name, gender, grade, score) VALUES (?, ?, ?, ?)")) {
    // 对同一个PreparedStatement反复设置参数并调用addBatch():
    for (Student s : students) {
        ps.setString(1, s.name);
        ps.setBoolean(2, s.gender);
        ps.setInt(3, s.grade);
        ps.setInt(4, s.score);
        ps.addBatch(); // 添加到batch
    }
    // 执行batch:
    int[] ns = ps.executeBatch();


    for (int n : ns) {
        System.out.println(n + " inserted."); // batch中每个SQL执行的结果数量
    }
}

执行 batch 和执行一个 SQL 不同点在于，需要对同一个 PreparedStatement 反复设置参数并调用 addBatch()，这样就相当于给一个 SQL 加上了多组参数，相当于变成了 “多行” SQL。

第二个不同点是调用的不是 executeUpdate()，而是 executeBatch()，因为我们设置了多组参数，相应地，返回结果也是多个 int 值，因此返回类型是 int[]，循环 int[] 数组即可获取每组参数执行后影响的结果数量。

CallableStatement​

CallableStatement 和 PreparedStatement用法特别相似，只是CallableStatement 可以用来调用存储过程。

SQL 语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

说白了，存储过程就是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。

TODO: 用到更新...

这篇博客介绍的不错：https://juejin.cn/post/6844903584174178311

ResultSet 封装结果​

封装了所有的查询结果 getDate getObject getString ... 知道具体的类型就是使用具体的方法，不知道类型则使用getObject

while (resultSet.next()){
    System.out.println("id="+resultSet.getObject("id"));
    System.out.println("name="+resultSet.getObject("username"));
    System.out.println("pwd="+resultSet.getObject("password"));
    System.out.println("email="+resultSet.getObject("email"));
    System.out.println("birthday="+resultSet.getObject("birthday"));
}

resultSet 查询数据的原理就是遍历指针

  • resultSet.next() 移动到下一个数据
  • resultSet.previous() 移动到前一行
  • resultSet.absolute() 移动到指定行
  • resultSet.beforeFirst(); 指针移动最前面
  • resultSet.afterLast(); 指针移动到最后面

抽象成工具类​

public class JdbcUtils {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {
        Properties properties = null;
        try (
                InputStream fileReader = JdbcUtils.class.getResourceAsStream("/urlPath.properties");
        ) {
            properties = new Properties();
            properties.load(fileReader);
            driver = Objects.requireNonNull(properties).getProperty("driver");
            url = Objects.requireNonNull(properties).getProperty("url");
            username = Objects.requireNonNull(properties).getProperty("username");
            password = Objects.requireNonNull(properties).getProperty("password");

            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException{
        return DriverManager.getConnection(url,username,password);
    }

    public static void release(Connection connection, PreparedStatement preparedStatement, ResultSet resultSet){
        try {
            if (resultSet!=null) {
                resultSet.close();
            }
            if (preparedStatement!=null) {
                preparedStatement.close();
            }
            if (connection!=null) {
                connection.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

JDBC操作事务​

使用到的表

-- 创建账号表
create table account(
    id int primary key auto_increment,
    name varchar(40),
    money float
);

insert into account(name, money) VALUES ('A',1000),('B',1000),('C',1000);

其中的 setAutoCommit(false) 除了关闭自动提交，其还会开启一个事务

public class TestTransaction1 {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement1 = null;
        PreparedStatement preparedStatement2 = null;
        try{
            connection = JdbcUtils.getConnection();
            // 关闭自动提交，事务会自动开启
            connection.setAutoCommit(false);
            String sql01 = "update account set money = money-100 where name = 'A'";
            String sql02 = "update account set money = money+100 where name = 'B'";
            preparedStatement1 = connection.prepareStatement(sql01);
            preparedStatement1.executeUpdate();
            preparedStatement2 = connection.prepareStatement(sql02);
            preparedStatement2.executeUpdate();

            // 成功就提交事务
            connection.commit();
        }catch (SQLException throwables) {
            // 失败了就回滚
            connection.rollback();
            throwables.printStackTrace();
        }finally {
            // 完成之后重新打开自动提交
            connection.setAutoCommit(true);
            JdbcUtils.release(connection,preparedStatement1,null);
            JdbcUtils.release(null,preparedStatement2,null);
        }
    }
}

数据库连接池​

传统模式：数据库连接 -- 执行完毕 -- 释放 但是连接与释放十分浪费系统资源 所以就有连接池技术：准备好预先的资源，过来就连接预先准备好的

从而就引申出三个概念

  • 最小连接数：5
  • 最大连接数：20 （业务最高承载上限）
  • 等待超时

当超过最大连接数时，就排队等待 等待超时：当连接超时之后直接让他断开连接

编写连接池，实现一个 DataSource 接口，这个接口只有一个方法 getConnection()

阿里的druid​

这里连接池第三方工具用的是阿里的druid

<dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>druid</artifactId>
     <version>1.1.16</version>
</dependency>

初始化

driverClassName=com.mysql.jdbc.Driver
# 需要设置时区以及字符编码，否则会报错
url=jdbc:mysql://localhost:3306/testuser?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
username=root
password=123456
# 设置初始连接数
initialSize=5
maxActive=10
maxWait=3000
...

private static DataSource dataSource;
//先通过properties来读取数据
static {
    Properties prop = new Properties();
    try {
        prop.load(DataSourceUnit.class.getResourceAsStream("/druid.properties"));
        //添加连接池（这里用的是阿里的连接池Druid）
        dataSource = DruidDataSourceFactory.createDataSource(prop);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

...

然后就可以直接调用 getConnection() 取得连接了

常见问题汇总​

驱动装载​

这行 Class.forName 干了什么？

Class.forName("com.mysql.cj.jdbc.Driver");

Class.forName 把这个类模板加载到 JVM 里

Reference​